package com.yx.changdao.common.utils;

import com.yx.changdao.common.data.ExcelSheetItem;
import com.yx.changdao.common.data.ExcelVersion;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.WorkbookUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;

/**
 * @Description: <br>
 * 导出 excel 工具类
 *
 * <p><br>
 * @Auther: Zhangjiashun
 * @create 2019/11/20 8:50
 */
public class ExportExcelUtils {

    public static final Logger log = LoggerFactory.getLogger(ExportExcelUtils.class);
    /**
     * 标题样式
     */
    private static final String STYLE_HEADER = "header";
    /**
     * 表头样式
     */
    private static final String STYLE_TITLE = "title";
    /**
     * 数据样式
     */
    private static final String STYLE_DATA = "data";

    /**
     * 存储样式
     */
    private static final HashMap<String, CellStyle> cellStyleMap = new HashMap<>();

    /**
     * 读取excel文件里面的内容 支持日期，数字，字符，函数公式，布尔类型
     *
     * @param file        文件
     * @param rowCount    行数
     * @param columnCount 列数
     */
    public static List<ExcelSheetItem> readExcel(File file, Integer rowCount, Integer columnCount)
            throws IOException {

        // 根据后缀名称判断excel的版本
        String extName = file.getName().substring(file.getName().lastIndexOf(".") + 1);
        Workbook wb;
        if (ExcelVersion.V2003.getSuffix().equals(extName)) {
            wb = new HSSFWorkbook(new FileInputStream(file));

        } else if (ExcelVersion.V2007.getSuffix().equals(extName)) {
            wb = new XSSFWorkbook(new FileInputStream(file));

        } else {
            // 无效后缀名称，这里之能保证excel的后缀名称，不能保证文件类型正确，不过没关系，在创建Workbook的时候会校验文件格式
            throw new IllegalArgumentException("Invalid excel version");
        }
        // 开始读取数据
        List<ExcelSheetItem> sheetItems = new ArrayList<>();
        // 解析sheet
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            Sheet sheet = wb.getSheetAt(i);
            List<List<Object>> dataList = new ArrayList<>();
            ExcelSheetItem sheetItem = new ExcelSheetItem();
            sheetItem.setSheetName(sheet.getSheetName());
            sheetItem.setDataList(dataList);
            int readRowCount;
            if (rowCount == null || rowCount > sheet.getPhysicalNumberOfRows()) {
                readRowCount = sheet.getPhysicalNumberOfRows();
            } else {
                readRowCount = rowCount;
            }
            // 解析sheet 的行
            for (int j = sheet.getFirstRowNum(); j < readRowCount; j++) {
                Row row = sheet.getRow(j);
                if (row == null) {
                    continue;
                }
                if (row.getFirstCellNum() < 0) {
                    continue;
                }
                int readColumnCount;
                if (columnCount == null || columnCount > row.getLastCellNum()) {
                    readColumnCount = row.getLastCellNum();
                } else {
                    readColumnCount = columnCount;
                }
                List<Object> rowValue = new LinkedList<>();
                // 解析sheet 的列
                for (int k = 0; k < readColumnCount; k++) {
                    Cell cell = row.getCell(k);
                    rowValue.add(getCellValue(wb, cell));
                }
                dataList.add(rowValue);
            }
            sheetItems.add(sheetItem);
        }
        return sheetItems;
    }

    private static Object getCellValue(Workbook wb, Cell cell) {
        Object columnValue = null;
        if (cell != null) {
            // 格式化 number
            DecimalFormat df = new DecimalFormat("0");
            // String
            // 字符
            // 格式化日期字符串
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            // 格式化数字
            DecimalFormat nf = new DecimalFormat("0.00");

            switch (cell.getCellTypeEnum()) {
                case STRING:
                    columnValue = cell.getStringCellValue();
                    break;
                case NUMERIC:
                    if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                        columnValue = df.format(cell.getNumericCellValue());
                    } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                        columnValue = nf.format(cell.getNumericCellValue());
                    } else {
                        columnValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                    }
                    break;
                case BOOLEAN:
                    columnValue = cell.getBooleanCellValue();
                    break;
                case BLANK:
                    columnValue = "";
                    break;
                case FORMULA:
                    // 格式单元格
                    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
                    evaluator.evaluateInCell(cell);
                    CellValue cellValue = evaluator.evaluate(cell);
                    columnValue = cellValue.getNumberValue();
                    break;
                default:
                    columnValue = cell.toString();
            }
        }
        return columnValue;
    }

    /**
     * 在硬盘上写入excel文件
     *
     * @param version     Excel版本
     * @param excelSheets 代写入SHeet
     * @param filePath    文件路径
     */
    public static void createWorkbookAtDisk(
            ExcelVersion version, List<ExcelSheetItem> excelSheets, String filePath) throws IOException {
        FileOutputStream fileOut = new FileOutputStream(filePath);
        createWorkbookAtOutStream(version, excelSheets, fileOut, true);
    }

    /**
     * 把excel表格写入输出流中，输出流会被关闭
     *
     * @param version     版本
     * @param excelSheets 代写入SHeet
     * @param outStream   输出流
     * @param closeStream 是否关闭输出流
     */
    public static void createWorkbookAtOutStream(
            ExcelVersion version,
            List<ExcelSheetItem> excelSheets,
            OutputStream outStream,
            boolean closeStream)
            throws IOException {
        if (CollectionUtils.isNotEmpty(excelSheets)) {
            Workbook wb = createWorkBook(version, excelSheets);
            wb.write(outStream);
            if (closeStream) {
                outStream.close();
            }
        }
    }

    private static Workbook createWorkBook(ExcelVersion version, List<ExcelSheetItem> excelSheets) {
        Workbook wb = createWorkbook(version);
        for (int i = 0; i < excelSheets.size(); i++) {
            ExcelSheetItem excelSheetItem = excelSheets.get(i);
            if (excelSheetItem.getSheetName() == null) {
                excelSheetItem.setSheetName("sheet" + i + 1);
            }
            // 过滤特殊字符
            Sheet tempSheet =
                    wb.createSheet(WorkbookUtil.createSafeSheetName(excelSheetItem.getSheetName()));
            buildSheetData(wb, tempSheet, excelSheetItem, version);
        }
        return wb;
    }

    private static void buildSheetData(
            Workbook wb, Sheet sheet, ExcelSheetItem excelSheetItem, ExcelVersion version) {

        if (excelSheetItem == null) {
            return;
        }

        sheet.setDefaultRowHeight((short) 400);
        sheet.setDefaultColumnWidth((short) 10);
        // 有需要可以自定义title和header
        createTitle(sheet, excelSheetItem, wb, version);
        createHeader(sheet, excelSheetItem, wb, version);
        createBody(sheet, excelSheetItem, wb, version);

        if (excelSheetItem.getDataList() != null && excelSheetItem.getDataList().size() != 0) {
            // 设置自动列宽
            for (int i = 0; i < excelSheetItem.getDataList().get(0).size(); i++) {
                sheet.autoSizeColumn(i);
            }
        }
    }

    /**
     * 内容
     *
     * @param sheet          sheet
     * @param excelSheetItem sheet 信息
     * @param wb             工作表格
     * @param version        版本
     */
    private static void createBody(
            Sheet sheet, ExcelSheetItem excelSheetItem, Workbook wb, ExcelVersion version) {
        List<List<Object>> dataList = excelSheetItem.getDataList();
        int cur = 1;
        if (StringUtils.isNotEmpty(excelSheetItem.getTitle())) {
            cur++;
        }
        for (int i = 0; i < dataList.size() && i < version.getMaxRow(); i++) {
            List<Object> values = dataList.get(i);
            Row row = sheet.createRow(cur + i);
            for (int j = 0; j < values.size() && j < version.getMaxColumn(); j++) {
                Cell cell = row.createCell(j);
                cell.setCellType(CellType.STRING);
                cell.setCellStyle(getStyle(STYLE_DATA, wb));
                cell.setCellValue(values.get(j).toString() + "\t");
            }
        }
    }

    /**
     * 生成header
     *
     * @param sheet          sheet
     * @param excelSheetItem sheet 内容
     * @param wb             工作表
     * @param version        版本
     */
    private static void createHeader(
            Sheet sheet, ExcelSheetItem excelSheetItem, Workbook wb, ExcelVersion version) {
        List<String> headers = excelSheetItem.getHeaders();
        int cur = 0;
        if (StringUtils.isNotEmpty(excelSheetItem.getTitle())) {
            cur++;
        }
        if (headers != null) {
            Row row = sheet.createRow(cur);
            for (int i = 0; i < headers.size() && i < version.getMaxColumn(); i++) {
                Cell cellHeader = row.createCell(i);
                cellHeader.setCellStyle(getStyle(STYLE_HEADER, wb));
                cellHeader.setCellValue(headers.get(i));
            }
        }
    }

    /**
     * 生成title
     *
     * @param sheet          sheet
     * @param excelSheetItem sheet 内容
     * @param wb             工作表
     * @param version        版本
     */
    private static void createTitle(
            Sheet sheet, ExcelSheetItem excelSheetItem, Workbook wb, ExcelVersion version) {
        if (StringUtils.isEmpty(excelSheetItem.getTitle())
                || excelSheetItem.getDataList().size() == 0) {
            return;
        }
        Row titleRow = sheet.createRow(0);
        Cell titleCel = titleRow.createCell(0);
        titleCel.setCellValue(excelSheetItem.getTitle());
        titleCel.setCellStyle(getStyle(STYLE_TITLE, wb));
        // 限制最大列数
        int column = Math.min(excelSheetItem.getDataList().get(0).size(), version.getMaxColumn());
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, column - 1));
    }

    /**
     * 获取样式style
     *
     * @return 单个单元格样式
     */
    private static CellStyle getStyle(String type, Workbook wb) {

        if (cellStyleMap.containsKey(type)) {
            return cellStyleMap.get(type);
        }
        // 生成一个样式
        CellStyle style = wb.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setWrapText(true);

        if (STYLE_HEADER.equals(type)) {
            style.setAlignment(HorizontalAlignment.CENTER);
            Font font = wb.createFont();
            font.setFontHeightInPoints((short) 16);
            font.setBold(true);
            style.setFont(font);
        } else if (STYLE_TITLE.equals(type)) {
            style.setAlignment(HorizontalAlignment.CENTER);
            Font font = wb.createFont();
            font.setFontHeightInPoints((short) 18);
            font.setBold(true);
            style.setFont(font);
        } else if (STYLE_DATA.equals(type)) {
            style.setAlignment(HorizontalAlignment.LEFT);
            Font font = wb.createFont();
            font.setFontHeightInPoints((short) 12);
            style.setFont(font);
        }
        cellStyleMap.put(type, style);
        return style;
    }

    private static Workbook createWorkbook(ExcelVersion version) {
        if (version == ExcelVersion.V2007) {
            return new XSSFWorkbook();
        }
        return new HSSFWorkbook();
    }

    /**
     * 设置 excel 响应头
     *
     * @param fileName 文件名
     * @param response 响应response
     */
    public static void excelResponseSetProperties(String fileName, HttpServletResponse response) {
        // 设置文件后缀
        String fn = fileName + ".xls";
        // 读取字符编码
        String charset = "UTF-8";

        // 设置响应
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setCharacterEncoding(charset);
        response.setHeader("Pragma", "public");
        response.setHeader("Cache-Control", "max-age=30");
        try {
            response.setHeader(
                    "Content-Disposition", "attachment; filename=" + URLEncoder.encode(fn, charset));
        } catch (UnsupportedEncodingException e) {
            log.error("转码异常 = ", e);
        }
    }
}
